/* Table 2 Panel A Immigration across regions */
clear all
/* cd "M:\ImmPanelRevis17\donnees\cells\"*/
use "C:\Users\Public\Documents\ImmPanelRevis19\ImmPanelRevis19\Donnees\indivm.dta", clear

/**************************************/
/* Définition des zones géographiques */
/**************************************/
/**************************************/
/* 1) => Merge avec les ZE 			  */
/**************************************/
/* elimine la corse */
drop if dep=="2A" | dep=="2B"

gen cgeo=dep+comt
sort cgeo
/* Paris */
replace cgeo="75056" if dep=="75"
/* Lyon */
replace cgeo="69123" if cgeo=="69381" | cgeo=="69382" | cgeo=="69383" | cgeo=="69384" | cgeo=="69385" | cgeo=="69386" | cgeo=="69387" | cgeo=="69388" | cgeo=="69389" 
/* Marseille */
replace cgeo="13055" if cgeo=="13201" | cgeo=="13202" | cgeo=="13203" | cgeo=="13204" | cgeo=="13205" | cgeo=="13206" | cgeo=="13207" | cgeo=="13208" | cgeo=="13209" | cgeo=="13210" | cgeo=="13211" | cgeo=="13212" | cgeo=="13213" | cgeo=="13214" | cgeo=="13215" | cgeo=="13216"

/* définit les ZE */
joinby cgeo using "C:\Users\Public\Documents\ImmPanelRevis19\ImmPanelRevis19\Donnees\cgeo_ze.dta", unm(m)
tab _merge
drop if _merge==1
drop zen

/* foreign born workers */
gen fborn=(depnai=="99")
gen ifborn="N" if fborn==0
replace ifborn="FB" if fborn==1
compress

drop if age<20
drop if age>55

/* all workers */
gen all=1
/* blue collar dummy */
gen ouv=(cs2h=="62" | cs2h=="63" | cs2h=="64" | cs2h=="65" | cs2h=="67" | cs2h=="68" | cs2h=="69" )
/* BLUE COLLARD construction */
gen constbc=(nap40=="24" & ouv==1)
/* BLUE COLLARD tradable */
gen trad=((nap40=="01" | nap40=="02" | ///
nap40=="03" | nap40=="04" | nap40=="05" | nap40=="06" | ///
nap40=="07" | nap40=="08" | nap40=="09" | nap40=="10" | ///
nap40=="11" | nap40=="12" | nap40=="13" | nap40=="14" | ///
nap40=="15" | nap40=="16" | nap40=="17" | nap40=="18" | ///
nap40=="19" | nap40=="20" | nap40=="21" | nap40=="22" | ///
nap40=="23" | nap40=="36" | nap40=="37" | nap40=="35" | nap40=="32") & ouv==1)
gen ntrad=(( nap40=="25" | ///
nap40=="26" | nap40=="27" | nap40=="28" | nap40=="29" | ///
nap40=="30" | nap40=="31" | nap40=="33" | nap40=="34") & ouv==1)
/* eliminate services non-marchand */
gen bc=(nap40!="38" & ouv==1)

/****************************************/
/* ouvriers qualitifés et non qualifiés */
/****************************************/
gen qual=(cs2_anc=="60" | cs2_anc=="61" ///
| cs2=="62" | cs2=="63"| cs2=="64"| cs2=="65")
/* low skilled BLUE COLLARD */
gen bcnq=bc*(1-qual)
gen constbcnq=constbc*(1-qual)
gen tradnq=trad*(1-qual)
gen ntradnq=ntrad*(1-qual)
/* Skilled BLUE COLLARD */
gen bcq=bc*(qual)
gen constbcq=constbc*(qual)
gen tradq=trad*(qual)
gen ntradq=ntrad*(qual)

/* Employees */
gen emp=(cs2h=="54" | cs2h=="55" | cs2h=="56")
/* Employees in the service sector */
gen empsc=(cs2h=="56")

/* Managers */
gen cad=(cs2h=="23" | cs2h=="31" | cs2h=="33" | cs2h=="34" | cs2h=="35" | cs2h=="37" | cs2h=="38")
/* Technicians */
gen tec=(cs2h=="42" | cs2h=="43" | cs2h=="44" | cs2h=="45" | cs2h=="46" | cs2h=="47" )

/* clerk */
gen clrk=(cs2h=="46")


gen rze=substr(ze,1,2)
gen paris=(cgeo=="75056")
gen lyon=(cgeo=="69123")
gen britt=(rze=="53")

tab fborn an , col
tab fborn an if cad, col
tab fborn an if clrk | tec, col
tab fborn an if bc, col
tab fborn an if trad, col
tab fborn an if ntrad, col
tab fborn an if constbc, col

drop dep
drop _merge
joinby cgeo using "C:\Users\Public\Documents\ImmPanelRevis19\ImmPanelRevis19\Donnees\cgeo_reg_dep.dta", unm(m)

/* occupations */
gen occup="bcq" if bcq
replace occup="bcnq" if bcnq
replace occup="cad" if cad
replace occup="tec/clrk" if tec | clrk
tab cs2h occup, m

/* TABLE 2 PANEL B */
/* share foreign born among employees */
tabstat fborn if an==1982, by(occup)
tabstat fborn if an==1991, by(occup)
tabstat fborn if an==1999, by(occup)
tabstat fborn if an==2007, by(occup)


tabstat fborn if an==2007 & trad
tabstat fborn if an==2007 & ntrad
tabstat fborn if an==2007 & constbc

save temp.dta, replace

/* TABLE 2 PANEL C */
drop if missing(occup)
/* share natives in the group */
tab occup if !fborn & an==2007
/* share foreign born in the group */
tab occup if fborn & an==2007

/* TABLE 2 PANEL A */
/* define new regions */
gen nreg="11" if reg=="11"
replace nreg="24" if reg=="24"
replace nreg="27" if reg=="26" | reg=="43"
replace nreg="28" if reg=="23" | reg=="25"
replace nreg="32" if reg=="31" | reg=="22"
replace nreg="44" if reg=="42" | reg=="21" | reg=="41"
replace nreg="52" if reg=="52" 
replace nreg="53" if reg=="53" 
replace nreg="75" if reg=="72" | reg=="74" | reg=="54"
replace nreg="76" if reg=="91" | reg=="73"
replace nreg="84" if reg=="83" | reg=="82"
replace nreg="93" if reg=="93" 
replace nreg="94" if reg=="94" 

tabstat fborn,  by(an)
tabstat fborn if nreg=="11",  by(an)
tabstat fborn if nreg=="44",  by(an)
tabstat fborn if nreg=="75",  by(an)
tabstat fborn if nreg=="84",  by(an)
tabstat fborn if nreg=="27",  by(an)
tabstat fborn if nreg=="53",  by(an)
tabstat fborn if nreg=="24",  by(an)
tabstat fborn if nreg=="76",  by(an)
tabstat fborn if nreg=="32",  by(an)
tabstat fborn if nreg=="28",  by(an)
tabstat fborn if nreg=="52",  by(an)
tabstat fborn if nreg=="93",  by(an)
tabstat fborn if nreg=="44",  by(an)
